--
-- Correcciones a las funciones de tiendas e-commerce estandar (TN y ML) 
--

DROP FUNCTION IF EXISTS tienda_ecommerce.get_jsonactualizacionstock(character varying, integer, integer, character varying);

CREATE OR REPLACE FUNCTION tienda_ecommerce.get_jsonactualizacionstock(
	ptienda character varying,
	pidcomm integer,
	psigno integer,
	pcodambsuc character varying,
	parametros character varying)
    RETURNS TABLE(xml character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 100

AS $BODY$
DECLARE
  id_tienda   varchar;
  cCursor    refCursor;
  vCadena       varchar;
  vCadenaNuevo  varchar;
  vArbolTN 		varchar;
  vComillaDoble varchar;
BEGIN
	INSERT INTO IMPORTACION.IMPXML VALUES (0, 'xml');
	
	-- vComillaDoble existe porque el migrador eliminaria el caracter de la comilla doble de los json al migrar
    vComillaDoble := chr(34);


  -- El id de la tienda está definido en la sucursal
  -- la TiendaStock de la sucursal puede redefinir la tienda de la que se saca el stock
  id_tienda := tienda_ecommerce.get_idtiendasucursal(pTienda, pCodAmbSuc);
  id_tienda := coalesce(tienda_ecommerce.get_idtiendastocksucursal(pTienda, pCodAmbSuc), id_tienda);
  
  vArbolTN  := case when pTienda = 'TN' then 
                        (SELECT TIPO FROM ARTA WHERE NOMBRE = 'SincTiendaNube')
    				when pTienda = 'ML' then 
                        (SELECT TIPO FROM ARTA WHERE NOMBRE = 'SincMercadoLibre')
                    else ''
					end;
  IF id_tienda IS NOT NULL AND vArbolTN IS NOT NULL THEN 
  	open cCursor for
  		SELECT '{' || vComillaDoble || 'sku' || vComillaDoble || ':' ||  vComillaDoble || trim(art.cod) ||  vComillaDoble || ',' 
		           || vComillaDoble || 'stock' ||  vComillaDoble || ':' || trim(cast(artm.cant * artm.signo * psigno as varchar)) || '}'
    	FROM ARTM
    	INNER JOIN ART ON ART.ID = ARTM.IDART
		INNER JOIN ARTC ON ARTC.TIPOARTA = vArbolTN AND ARTC.IDART = ART.ID AND ARTC.CODARTR = 'STN' 
		INNER JOIN COMM ON COMM.ID = ARTM.IDCOMM
    	WHERE ARTM.IDCOMM = pIdComm AND (ARTM.MODIFICASTOCK = 'S' OR COMM.TIPOCOM = 'PTN');
		
  		vCadena := '[';
 
  	fetch cCursor into vCadenaNuevo;
  	while found loop
    	vCadena := vCadena || vCadenaNuevo || ',' ;  
     	fetch cCursor into vCadenaNuevo;
  	end loop;
  	close cCursor;
  	if length(vCadena) > 1 then
		vCadena := trim(vCadena,',');
		vCadena := vCadena || ']';
  	end if;
	IF vCadena <> '[' THEN
	    INSERT INTO IMPORTACION.IMPXML VALUES (1, 'SELECT CAST(''' || ltrim(id_tienda,'0') || '/act_stock/' || vCadena || ''' AS VARCHAR(64000)) as xml');
	   	RETURN QUERY EXECUTE ('SELECT CAST(''' || ltrim(id_tienda,'0') || '/act_stock/' || vCadena || ''' AS VARCHAR(64000)) as xml');
	  ELSE
	    INSERT INTO IMPORTACION.IMPXML VALUES (2, 'SELECT CAST(''' || ltrim(id_tienda,'0') || '/act_stock/' || vCadena || ''' AS VARCHAR(64000)) as xml');
		RETURN QUERY EXECUTE ('SELECT CAST('''' AS VARCHAR(64000)) as xml');
	END IF;
  ELSE
	    INSERT INTO IMPORTACION.IMPXML VALUES (3, 'SELECT CAST(''' || ltrim(id_tienda,'0') || '/act_stock/' || vCadena || ''' AS VARCHAR(64000)) as xml');
	RETURN QUERY EXECUTE ('SELECT CAST('''' AS VARCHAR(64000)) as xml');
  END IF; 
END;
$BODY$;

ALTER FUNCTION tienda_ecommerce.get_jsonactualizacionstock(character varying, integer, integer, character varying, character varying)
    OWNER TO abacouser;



--
SELECT aaVersion('508.03', '508.02');
--



